from flask import jsonify, render_template, request

#
from blues.cloud import bp
from database import session_maker

# 组织维度- ECS综合查询
ecs_sql = """
SELECT
	x.org AS `组织`,
	x.`CPU最大使用率%`,
	x.`CPU最小使用率%`,
	x.`CPU平均使用率%`,
	x.`内存最大使用率%`,
	x.`内存最小使用率%`,
	x.`内存平均使用率%`,
	y.`磁盘使用率%` 
FROM
	(
	SELECT
		org,
		project,
		ROUND( 100 * sum( cpu_max_used ) / sum( cpu ), 2 ) AS 'CPU最大使用率%',
		ROUND( 100 * sum( cpu_min_used ) / sum( cpu ), 2 ) AS 'CPU最小使用率%',
		ROUND( 100 * SUM( cpu_avg_used ) / sum( cpu ), 2 ) AS 'CPU平均使用率%',
		ROUND( 100 * SUM( mem_max_used ) / sum( memory ), 2 ) AS '内存最大使用率%',
		ROUND( 100 * SUM( mem_min_used ) / sum( memory ), 2 ) AS '内存最小使用率%',
		ROUND( 100 * SUM( mem_avg_used ) / sum( memory ), 2 ) AS '内存平均使用率%' 
	FROM
		(
		SELECT
		CASE
				a.org 
				WHEN '工程院-城市大脑' THEN
				'工程院' 
				WHEN '工程院-运维测试' THEN
				'工程院' 
				WHEN '工程院-郑冠杰' THEN
				'工程院' 
				WHEN '工程院-2050' THEN
				'工程院' 
				WHEN '工程院-中枢系统' THEN
				'工程院' ELSE a.org 
			END AS org,
			a.project,
			a.instance_id,
			c.cpu,
			c.memory,
			ROUND( c.cpu * b.cpu_max / 100, 3 ) AS cpu_max_used,
			ROUND( c.cpu * b.cpu_min / 100, 3 ) AS cpu_min_used,
			ROUND( c.cpu * b.cpu_avg / 100, 3 ) AS cpu_avg_used,
			ROUND( c.memory * b.mem_max / 100, 3 ) AS mem_max_used,
			ROUND( c.memory * b.mem_min / 100, 3 ) AS mem_min_used,
			ROUND( c.memory * b.mem_avg / 100, 3 ) AS mem_avg_used 
		FROM
			cmdb_cloud_resource a,
			cms_ecs b,
			c_ecs_info c 
		WHERE
			a.instance_id = b.instance_id 
			AND a.instance_id = c.instance_id 
			AND a.is_deleted IS NULL 
			AND a.res_type = 'ECS' 
			AND b.date BETWEEN '{}' 
			AND '{}' 
			AND DATE_FORMAT( c.record_time, '%Y-%m-%d' ) = b.date 
		) ecs_all 
	GROUP BY
		org 
	) x
	LEFT JOIN (
	SELECT
		org,
		ROUND( SUM( disk_used_100 ) / SUM( disk_size ), 2 ) AS '磁盘使用率%' 
	FROM
		(
		SELECT
		CASE
				a.org 
				WHEN '工程院-城市大脑' THEN
				'工程院' 
				WHEN '工程院-运维测试' THEN
				'工程院' 
				WHEN '工程院-郑冠杰' THEN
				'工程院' 
				WHEN '工程院-2050' THEN
				'工程院' 
				WHEN '工程院-中枢系统' THEN
				'工程院' ELSE a.org 
			END AS org,
			a.instance_id,
			b.disk_id,
			b.disk_size,
			b.disk_usage,
			b.disk_size * b.disk_usage AS disk_used_100 
		FROM
			cmdb_cloud_resource a,
			cms_ecs_disk b 
		WHERE
			a.instance_id = b.instance_id 
			AND a.is_deleted IS NULL 
			AND b.date = ( SELECT MAX( date ) FROM cms_ecs_disk WHERE date <= '{}' ) 
		) disk_all 
	GROUP BY
	org 
	) y ON x.org = y.org
"""

# 组织维度- RDS综合查询
rds_sql = """
SELECT
	x.org AS '组织',
	ROUND( SUM( x.cpu_max_used_100 ) / SUM( x.cpu ), 2 ) AS 'CPU最大使用率%',
	ROUND( SUM( x.cpu_min_used_100 ) / SUM( x.cpu ), 2 ) AS 'CPU最小使用率%',
	ROUND( SUM( x.cpu_avg_used_100 ) / SUM( x.cpu ), 2 ) AS 'CPU平均使用率%',
	ROUND( SUM( x.mem_max_used_100 ) / SUM( x.mem ), 2 ) AS '内存最大使用率%',
	ROUND( SUM( x.mem_min_used_100 ) / SUM( x.mem ), 2 ) AS '内存最小使用率%',
	ROUND( SUM( x.mem_avg_used_100 ) / SUM( x.mem ), 2 ) AS '内存平均使用率%',
	ROUND( SUM( x.conn_max_used_100 ) / SUM( x.conn_max ), 2 ) AS '连接数最大使用率%',
	ROUND( SUM( x.conn_min_used_100 ) / SUM( x.conn_max ), 2 ) AS '连接数最小使用率%',
	ROUND( SUM( x.conn_avg_used_100 ) / SUM( x.conn_max ), 2 ) AS '连接数平均使用率%',
	ROUND( AVG( x.disk_max_used_100 ), 2 ) AS '磁盘平均使用率%' 
FROM
	(
	SELECT
	CASE
			a.org 
			WHEN '工程院-城市大脑' THEN
			'工程院' 
			WHEN '工程院-运维测试' THEN
			'工程院' 
			WHEN '工程院-郑冠杰' THEN
			'工程院' 
			WHEN '工程院-2050' THEN
			'工程院' 
			WHEN '工程院-中枢系统' THEN
			'工程院' ELSE a.org 
		END AS org,
		a.instance_id,
		b.db_instance_class,
		c.cpu,
		c.mem,
		c.conn_max,
		d.date,
		c.cpu * d.cpu_max AS cpu_max_used_100,
		c.cpu * d.cpu_min AS cpu_min_used_100,
		c.cpu * d.cpu_avg AS cpu_avg_used_100,
		c.mem * d.mem_max AS mem_max_used_100,
		c.mem * d.mem_min AS mem_min_used_100,
		c.mem * d.mem_avg AS mem_avg_used_100,
		c.conn_max * d.con_max AS conn_max_used_100,
		c.conn_max * d.con_min AS conn_min_used_100,
		c.conn_max * d.con_avg AS conn_avg_used_100,
		d.disk_max AS disk_max_used_100 
	FROM
		cmdb_cloud_resource a,
		c_rds_info b,
		( SELECT db_instance_class, cpu, mem, conn_max FROM class_rds GROUP BY db_instance_class, cpu, mem, conn_max ) c,
		cms_rds d 
	WHERE
		a.res_type = 'RDS' 
		AND a.is_deleted IS NULL 
		AND a.instance_id = b.db_instance_id 
		AND a.instance_id = d.instance_id 
		AND d.date BETWEEN '{}' 
		AND '{}'
		AND b.db_instance_class = c.db_instance_class 
	) x 
GROUP BY
	x.org;
"""

# 组织维度- OSS综合查询， 按照截止时间来获取最新的数据
oss_sql = """
SELECT
	x.org AS '组织',
	ROUND( 100 * SUM( x.size_in_bytes ) / SUM( x.capacity_in_bytes ), 2 ) AS 'OSS存储使用率%'
FROM
	(
	SELECT
	CASE
			a.org
			WHEN '工程院-城市大脑' THEN
			'工程院'
			WHEN '工程院-运维测试' THEN
			'工程院'
			WHEN '工程院-郑冠杰' THEN
			'工程院'
			WHEN '工程院-2050' THEN
			'工程院'
			WHEN '工程院-中枢系统' THEN
			'工程院' ELSE a.org
		END AS org,
		a.instance_id,
		b.capacity,
	IF
		( b.capacity =- 1, 100, b.capacity ) * 1000000000 AS capacity_in_bytes,
		b.size_in_bytes
	FROM
		cmdb_cloud_resource a,
		c_oss_info b
	WHERE
		a.instance_id = b.bucket
		AND a.is_deleted IS NULL
		AND DATE_FORMAT( b.record_time, '%Y-%m-%d' ) = ( SELECT DATE_FORMAT( MAX( record_time ), '%Y-%m-%d' ) FROM c_oss_info WHERE DATE_FORMAT( record_time, '%Y-%m-%d' ) <= '{}' ) # 取截止范围内最新的数据
	) x
GROUP BY
	x.org;
"""

# 组织维度 - EIP带宽查询
eip_sql = """
SELECT
	x.org AS '组织',
	ROUND( 100 * SUM( x.net_rx_max ) / SUM( x.bandwidth_in_byte ), 2 ) AS '带宽最大使用率%',
	ROUND( 100 * SUM( x.net_rx_min ) / SUM( x.bandwidth_in_byte ), 2 ) AS '带宽最小使用率%',
	ROUND( 100 * SUM( x.net_rx_avg ) / SUM( x.bandwidth_in_byte ), 2 ) AS '带宽平均使用率%' 
FROM
	(
	SELECT
	CASE
			a.org 
			WHEN '工程院-城市大脑' THEN
			'工程院' 
			WHEN '工程院-运维测试' THEN
			'工程院' 
			WHEN '工程院-郑冠杰' THEN
			'工程院' 
			WHEN '工程院-2050' THEN
			'工程院' 
			WHEN '工程院-中枢系统' THEN
			'工程院' ELSE a.org 
		END AS org,
		a.instance_id,
		b.net_rx_max,
		b.net_rx_min,
		b.net_rx_avg,
		b.bandwidth * 1000000 AS bandwidth_in_byte 
	FROM
		cmdb_cloud_resource a,
		cms_eip b 
	WHERE
		a.res_type = 'EIP' 
		AND a.is_deleted IS NULL 
		AND a.instance_id = b.instance_id 
		AND b.date BETWEEN '{}' 
		AND '{}' # 获取时间范围内数据
	) x 
GROUP BY
	x.org;
"""

#  组织维度- ODPS综合查询， 注意查询时间格式 不带横线，如：20230901
odps_sql = """
SELECT
	z.org AS '组织',
	SUM( z.cpu_hour ) AS '本月消耗CPU核数合计(核*小时)',
	SUM( z.mem_hour ) AS '本月消耗内存合计(GB*小时)',
	SUM( z.total_task ) AS '本月合计任务数',
	SUM( z.disk_used_g ) AS '存储使用量(GB)',
	SUM( z.disk_quota_g ) AS '存储配额(GB)',
	ROUND( SUM( z.disk_used_g ) / SUM( z.disk_quota_g ) * 100, 2 ) AS '存储使用率%'
FROM
	(
	SELECT
	CASE
			y.org 
			WHEN '工程院-城市大脑' THEN
			'工程院' 
			WHEN '工程院-运维测试' THEN
			'工程院' 
			WHEN '工程院-郑冠杰' THEN
			'工程院' 
			WHEN '工程院-2050' THEN
			'工程院' 
			WHEN '工程院-中枢系统' THEN
			'工程院' ELSE y.org 
		END AS org,
		y.project,
		x.* 
	FROM
		(
		SELECT
			a.project AS odps_project,
			ROUND( IFNULL( SUM( a.total_cost_cpu ), 0 ) / 100 / 3600, 0 ) AS cpu_hour,
			ROUND( IFNULL( SUM( a.total_cost_mem ), 0 ) / 1024 / 3600, 0 ) AS mem_hour,
			IFNULL( SUM( a.total_task_num ), 0 ) AS total_task,
			ROUND( b.data_filelogicallength / 1024 / 1024 / 1024, 2 ) AS disk_used_g,
			ROUND( b.project_quota_size / 1024 / 1024 / 1024 / 3, 0 ) AS disk_quota_g 
		FROM
			abm_saas_ag.odps_project_info_topn a,
			abm_saas_ag.region_odps_project_storage_info_realtime b 
		WHERE
			a.project = b.project 
			AND a.ds BETWEEN '{}' 
			AND '{}' 
		GROUP BY
			a.project 
		) x
		RIGHT JOIN ( SELECT org, project, instance_id FROM `op-admin`.cmdb_cloud_resource WHERE res_type = 'ODPS' AND is_deleted IS NULL ) y ON x.odps_project = y.instance_id 
	) z 
GROUP BY
	z.org;
"""


def ecs_org_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(ecs_sql.format(start, end, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'cpu_max_usage': row[1], 'cpu_min_usage': row[2], 'cpu_avg_usage': row[3],
                'mem_max_usage': row[4], 'mem_min_usage': row[5], 'mem_avg_usage': row[6], 'disk_usage': row[7],
            }
            data_li.append(tmp_dict)
    return data_li


def rds_org_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(rds_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'cpu_max_usage': row[1], 'cpu_min_usage': row[2], 'cpu_avg_usage': row[3],
                'mem_max_usage': row[4], 'mem_min_usage': row[5], 'mem_avg_usage': row[6],
                'con_max_usage': row[7], 'con_min_usage': row[8], 'con_avg_usage': row[9], 'disk_usage': row[10]
            }
            data_li.append(tmp_dict)
    return data_li


def oss_org_stats(end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(oss_sql.format(end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'oss_use_percent': row[1]
            }
            data_li.append(tmp_dict)
    return data_li


def eip_org_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(eip_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'bandwidth_max_usage': row[1], 'bandwidth_min_usage': row[2],
                'bandwidth_avg_usage': row[3],
            }
            data_li.append(tmp_dict)
    return data_li


def odps_org_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(odps_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'cpu_core_hours': row[1], 'mem_gb_hours': row[2], 'tasks': row[3],
                'storage_used_size_gb': row[4], 'storage_quota_gb': row[5], 'storage_usage': row[6]
            }
            data_li.append(tmp_dict)
    return data_li


@bp.route('/api/v1/cloud/ecs_org_stats', methods=['GET'])
def ecs_org_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print("ecs org stats range:", start, end)
    data_li = ecs_org_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/rds_org_stats', methods=['GET'])
def rds_org_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = rds_org_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/oss_org_stats', methods=['GET'])
def oss_org_stats_api():
    end = request.args.get('end')  # 2023-09-20
    data_li = oss_org_stats(end)  # 根据截止时间，获取最新的OSS容量信息
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/eip_org_stats', methods=['GET'])
def eip_org_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = eip_org_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/odps_org_stats', methods=['GET'])
def odps_org_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    # odps的日期传参需要删除多余的符号"-"
    start = start.replace("-", "")
    end = end.replace("-", "")
    print(start, end)
    data_li = odps_org_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


# 渲染web页面
@bp.route('/cloud/org_stats')
def resource_org_stats_page():
    return render_template('resource_org_stats.html', active='cloud_org')
